<?php

class openapi_data_original_externalsys{
    
    /**
    * 查询发货单及明细
    * @access public
    * @param mixed $filter 过滤条件
    * @param String $cols 订单号、订单时间区间、店铺、发货单号、物流单号
    * @return Array 订单、发货时间、发货单号、物流公司、物流单号、重量，物料明细
    */
    public function getdeliverylist($filter,$offset=0,$limit=100)
    {

        $db = kernel::database();
        $where[] = "d.status not in('cancel','back','stop','return_back') ";
      
        if ($filter['order_bn']) 
        {
            $where[] = " os.order_bn='".$filter['order_bn']."'";
        }    
        if ($filter['relate_order_bn']) 
        {
            $where[] = " os.relate_order_bn='".$filter['relate_order_bn']."'";
        }  
        if ($filter['create_starttime']) {
            $where[] = " os.createtime>=".strtotime($filter['create_starttime']);
        }
        if ($filter['create_endtime']) {
            $where[] = " os.createtime<".strtotime($filter['create_endtime']);
        }
        if ($filter['shop_id']) 
        {
            $where[] = " f.shop_bn='".$filter['shop_id']."'";
        }
        if ($filter['delivery_bn']) 
        {
            $where[] = " e.delivery_bn='".$filter['delivery_bn']."'";
        }
        if ($filter['logi_no']) 
        {
            $where[] = " bl.logi_no='".$filter['logi_no']."'";
        }
        $sql = 'SELECT count(os.order_id) _c
              from sdb_ome_orders os 
              inner join sdb_ome_delivery_order odo on (odo.order_id = os.order_id)
              inner join sdb_ome_delivery od on ( od.delivery_id = odo.delivery_id ) 
              inner join sdb_wms_delivery d on ( d.outer_delivery_bn = od.delivery_bn ) 
              inner join sdb_wms_delivery_bill bl on d.delivery_id=bl.delivery_id
              inner join sdb_ome_shop f on os.shop_id=f.shop_id 
              where ' . implode(' AND ', $where);

        $count = $db->selectrow($sql);
        if(intval($count['_c']) >0){
            
            $deliveryLists = $db->select("SELECT os.ship_status,os.order_bn,d.delivery_time,d.delivery_id,d.delivery_bn,d.logi_name,bl.logi_no,d.net_weight
              from sdb_ome_orders os 
              inner join sdb_ome_delivery_order odo on (odo.order_id = os.order_id)
              inner join sdb_ome_delivery od on ( od.delivery_id = odo.delivery_id ) 
              inner join sdb_wms_delivery d on ( d.outer_delivery_bn = od.delivery_bn ) 
              inner join sdb_wms_delivery_bill bl on d.delivery_id=bl.delivery_id  
              inner join sdb_ome_shop f on os.shop_id=f.shop_id 
              WHERE " . implode(' AND ', $where) ." order by os.createtime asc limit ".$offset.",".$limit."");
            $deliveryInfos = array();

            foreach ( $deliveryLists as $delivery ) 
            {
                $delivery_id = $delivery['delivery_id'];
                $deliveryInfos[$delivery_id]['order_bn'] = $delivery['order_bn'];
                $deliveryInfos[$delivery_id]['ship_status'] = $delivery['ship_status'];
                $deliveryInfos[$delivery_id]['delivery_time'] = $delivery['delivery_time'];
                $deliveryInfos[$delivery_id]['delivery_bn'] = $delivery['delivery_bn'];
                $deliveryInfos[$delivery_id]['logi_name'] = $delivery['logi_name'];
                $deliveryInfos[$delivery_id]['logi_no'] = $delivery['logi_no'];
                $deliveryInfos[$delivery_id]['net_weight'] = $delivery['net_weight'];
                $deliveryInfos[$delivery_id]['items'] = array();

                $delivery_items = $db->select("select bn,number,product_name from sdb_wms_delivery_items where delivery_id=$delivery_id");
                foreach ($delivery_items as $k =>$delivery_item)
                {
                    $delivery_item['bn']= $delivery_item['bn'];
                    $delivery_item['product_name']= $delivery_item['product_name'];
                    $delivery_item['number']= $delivery_item['number'];
                    $deliveryInfos[$delivery_id]['items'][$k]=$delivery_item;
                }
            }
             return array(
                'lists' => $deliveryInfos,
                'count' => $count['_c'],
            );
        }else{
            return array(
                'lists' => array(),
                'count' => 0,
            );
        }
    }
    /**
    * 查询订单及明细
    * @access public
    * @param mixed $filter 过滤条件
    * @param String $cols 订单号、订单时间区间、店铺
    * @return Array 管易、是否已发货、到账、发票抬头、发票内容、店铺类型、店铺名称、系统单据编号、平台单号、定单类型、拍单时间、付款时间、会员代码、会员名称、建议仓库、建议快递、商品代码、商品名称、规格代码、规格名称、商品备注、预计发货时间、订购数、发货数、总重量、实际单价、实际金额、买家备注、卖家备注、收货人、收货人手机、收货地址、邮编、制单人、审核人、平台商品名称、州省、区市、区县
    */
    public function getorderlist($filter,$offset=0,$limit=100)
    {

        $db = kernel::database();
        $where[] = " os.disabled='false' AND os.is_fail='false' ";
      
        if ($filter['order_bn']) 
        {
            $where[] = " os.order_bn='".$filter['order_bn']."'";
        }   
        if ($filter['relate_order_bn']) 
        {
            $where[] = " os.relate_order_bn='".$filter['relate_order_bn']."'";
        }     
        if ($filter['create_starttime']) {
            $where[] = " os.createtime>=".strtotime($filter['create_starttime']);
        }
        if ($filter['create_endtime']) {
            $where[] = " os.createtime<".strtotime($filter['create_endtime']);
        }
        if ($filter['shop_id']) 
        {
            $where[] = " f.shop_bn='".$filter['shop_id']."'";
        }
        $sql = 'SELECT count(os.order_id) _c
            from sdb_ome_orders os 
            inner join sdb_ome_shop f on os.shop_id=f.shop_id 
            left join sdb_ome_members m on os.member_id=m.member_id 
            inner join sdb_ome_order_items item on item.order_id=os.order_id
            left join sdb_ome_dly_corp cp on os.logi_id=cp.corp_id
            inner join sdb_material_basic_material_ext smbme on item.product_id=smbme.bm_id
            left join sdb_pam_account act on act.account_id=os.op_id
            where ' . implode(' AND ', $where);
        $count = $db->selectrow($sql);
        if(intval($count['_c']) >0){
            
            $orderLists = $db->select("SELECT os.ship_status,os.pay_status,f.shop_type,f.`name` shop_name,os.relate_order_bn,
            os.order_bn,os.order_type,os.createtime,os.paytime,m.uname ucode,m.`name` uname,cp.`name` logi_name,
            item.bn,item.`name` product_name,smbme.specifications speccode,smbme.specifications specname,
            item.nums,item.sendnum,item.nums*smbme.weight net_weight,item.divide_order_fee/item.nums divideprice,
            item.divide_order_fee,os.custom_mark,os.ship_name,os.ship_mobile,os.ship_addr,os.ship_zip,act.login_name,
            os.ship_area
            from sdb_ome_orders os 
            inner join sdb_ome_shop f on os.shop_id=f.shop_id 
            left join sdb_ome_members m on os.member_id=m.member_id 
            inner join sdb_ome_order_items item on item.order_id=os.order_id
            left join sdb_ome_dly_corp cp on os.logi_id=cp.corp_id
            inner join sdb_material_basic_material_ext smbme on item.product_id=smbme.bm_id
            left join sdb_pam_account act on act.account_id=os.op_id 
              WHERE " . implode(' AND ', $where) ." order by os.createtime asc limit ".$offset.",".$limit."");
            $orderInfos = array();
            foreach ($orderLists as $key => $value) 
            {
                $orderInfos[$key]['ship_status'] = $value['ship_status'];
                $orderInfos[$key]['pay_status'] = $value['pay_status'];
                $orderInfos[$key]['shop_type'] = $value['shop_type'];
                $orderInfos[$key]['shop_name'] = $value['shop_name'];
                $orderInfos[$key]['relate_order_bn'] = $value['relate_order_bn'];
                $orderInfos[$key]['order_bn'] = $value['order_bn'];
                $orderInfos[$key]['order_type'] = $value['order_type'];
                $orderInfos[$key]['createtime'] = $value['createtime'];
                $orderInfos[$key]['paytime'] = $value['paytime'];
                $orderInfos[$key]['ucode'] = $value['ucode'];
                $orderInfos[$key]['uname'] = $value['uname'];
                $orderInfos[$key]['logi_name'] = $value['logi_name'];
                $orderInfos[$key]['bn'] = $value['bn'];
                $orderInfos[$key]['product_name'] = $value['product_name'];
                $orderInfos[$key]['speccode'] = $value['speccode'];
                $orderInfos[$key]['specname'] = $value['specname'];
                $orderInfos[$key]['nums'] = $value['nums'];
                $orderInfos[$key]['sendnum'] = $value['sendnum'];
                $orderInfos[$key]['net_weight'] = $value['net_weight'];
                $orderInfos[$key]['divideprice'] = $value['divideprice'];
                $orderInfos[$key]['divide_order_fee'] = $value['divide_order_fee'];
                $orderInfos[$key]['custom_mark'] = $value['custom_mark'];
                $orderInfos[$key]['ship_name'] = $value['ship_name'];
                $orderInfos[$key]['ship_mobile'] = $value['ship_mobile'];
                $orderInfos[$key]['ship_addr'] = $value['ship_addr'];
                $orderInfos[$key]['ship_zip'] = $value['ship_zip'];
                $orderInfos[$key]['login_name'] = $value['login_name'];

                list($area_prefix,$area_chs,$area_id) = explode(':',$value['ship_area']);
                $temp_area    = explode('/',$area_chs);
                $orderInfos[$key]['ship_province'] = $temp_area[0];
                $orderInfos[$key]['ship_city'] = $temp_area[1];
                $orderInfos[$key]['ship_district'] = $temp_area[2];
            }
             return array(
                'lists' => $orderInfos,
                'count' => $count['_c'],
            );
        }else{
            return array(
                'lists' => array(),
                'count' => 0,
            );
        }
    }
    
}